﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace NoORM.Core.DbMeta
{
    public class SqlMetaProvider : IMetaProvider
    {
        public List<TableMeta> GetTables(string connectionString)
        {
            List<TableMeta> result = new List<TableMeta>();
            string sql = @"
SELECT 
    sys.objects.name TableName,
    sys.extended_properties.value Description 
FROM sys.objects 
LEFT JOIN sys.extended_properties 
    ON (sys.objects.object_id=sys.extended_properties.major_id 
    AND sys.extended_properties.minor_id = 0) 
WHERE sys.objects.type='U' 
ORDER BY sys.extended_properties.name";
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                conn.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        TableMeta meta = new TableMeta();
                        meta.TableName = $"{dr["TableName"]}";
                        meta.Description = $"{dr["Description"]}";
                        result.Add(meta);
                    }
                }
            }
            return result;
        }
        public List<FieldMeta> GetFields(string connectionString, string tableName)
        {
            List<FieldMeta> list = new List<FieldMeta>();
            string sql =
@"SELECT 
    Tables.name TableName,
	Columns.name FieldName,
    Types.name TypeName,
    Columns.is_identity IsIdentity,
    Columns.is_nullable IsNullable,
	sys.extended_properties.value Description
FROM sys.columns As Columns
INNER JOIN sys.objects AS Tables ON(Columns.object_id=Tables.object_id AND Tables.type='U')
INNER JOIN sys.types AS Types ON(Columns.user_type_id=Types.user_type_id)
LEFT JOIN sys.extended_properties ON (Columns.column_id=sys.extended_properties.minor_id AND Columns.object_id=sys.extended_properties.major_id AND sys.extended_properties.name='MS_Description') 
WHERE Tables.name=@tableName ORDER BY Columns.column_id";
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter("@tableName", tableName));
                conn.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        FieldMeta meta = new FieldMeta();
                        meta.TableName = $"{dr["TableName"]}";
                        meta.FieldName = $"{dr["FieldName"]}";
                        meta.TypeName = $"{dr["TypeName"]}";
                        meta.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);
                        //meta.IsPrimaryKey = false;
                        meta.IsNullable = Convert.ToBoolean(dr["IsNullable"]);
                        meta.Description = $"{dr["Description"]}";
                        list.Add(meta);
                    }
                }
            }
            List<string> keys = this.GetPrimaryKeys(connectionString, tableName);
            foreach(FieldMeta field in list)
            {
                field.IsPrimaryKey = keys.Contains(field.FieldName);
            }
            return list;
        }

        private List<string> GetPrimaryKeys(string connectionString, string tableName)
        {
            List<string> result = new List<string>();
            string sql =
@"SELECT Columns.name FROM sys.indexes AS Indexes
    INNER JOIN sys.index_columns AS IndexColumns ON Indexes.object_id = IndexColumns.object_id AND Indexes.index_id = IndexColumns.index_id
    INNER JOIN sys.columns AS Columns ON Columns.object_id = IndexColumns.object_id AND Columns.column_id = IndexColumns.column_id
    INNER JOIN sys.objects AS IndexObject ON IndexObject.name = Indexes.name AND IndexObject.type = 'PK'
	INNER JOIN sys.objects AS TableObject ON Columns.object_id = TableObject.object_id AND TableObject.type='U'
WHERE TableObject.name=@tableName";
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter("@tableName", tableName));
                conn.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result.Add(dr[0].ToString());
                    }
                }
            }
            return result;
        }
    }
}
